import time
import pickle
import win32api
import pymysql

MYSQL_CONFIG = {
    'host':"localhost",
    'port':3306,
    'user':'root',
    'password':'root',
    'db':'rj_hospital',
}

def today(d=0):
    return time.strftime('%Y-%m-%d',time.localtime(time.time()+60*60*24*d))


def now():
    return time.strftime('%Y-%m-%d %H:%M:%S')


def file_save(filename,datas):
    with open(filename,'wb') as f:
        pickle.dump(datas,f)
        
    
def file_load(filename):
    try:
        with open(filename,'rb') as f:
            datas = pickle.load(f)
    except Exception as e:
        print('读取失败：',e)
        datas = None
    finally:
        return datas
    

def print_file(filename):
    win32api.ShellExecute(0, "print", filename, None, ".", 0)

def get_connection(host=MYSQL_CONFIG.get('host'), port=MYSQL_CONFIG.get('port'), user=MYSQL_CONFIG.get('user'), password=MYSQL_CONFIG.get('password'), db=MYSQL_CONFIG.get('db')):
    mysql_connect = pymysql.connect(
        host=host, port=port, db=db, user=user, password=password)
    return mysql_connect

class Db():
    def __init__(self, mysql_config = MYSQL_CONFIG) -> None:
        self.__host = mysql_config.get('host')
        self.__port = mysql_config.get('port')
        self.__user = mysql_config.get('user')
        self.__password = mysql_config.get('password')
        self.__db = mysql_config.get('db')

    def __enter__(self):
        connect = get_connection(host=self.__host, port=self.__port,
                                 db=self.__db, user=self.__user, password=self.__password)
        #下面加参数返回字典，不加参数返回元组
        cursor = connect.cursor(pymysql.cursors.DictCursor)
        # cursor = connect.cursor()
        connect.autocommit = False
        self._connect = connect
        self._cursor = cursor
        self.connect = connect
        return self

    #必须带四个参数，否则报错
    def __exit__(self,exc_type, exc_val, exc_tb):
        self._connect.commit()
        self._cursor.close()
        self._connect.close()

    @property
    def cursor(self):
        return self._cursor 

def get_mysql_data(sql):
    with Db() as db:
        try:
            db.cursor.execute(sql)
            result = db.cursor.fetchall()
            # title = [field[0] for field in db.cursor.description]
        except Exception as e:
            print(e)
            db.connect.rollback()
            return
        return result
    
def post_mysql(*sqls):    
    with Db() as db:
        for sql in sqls:
            try:
                db.cursor.execute(sql)
            except Exception as e:
                db.connect.rollback()
                print(e)
  
def replace_mysql(tablename,datas):
    print(f'{now()} 共有{len(datas)}条数据')
    with Db() as db:
        for data in datas:
            replace_sql = f"replace INTO {tablename} ({','.join([f'`{item}`' for item in data.keys() if data.get(item)])}) VALUES ({','.join([f'"{item}"' for item in data.values() if item])})"
            try:
                # print(replace_sql)
                db.cursor.execute(replace_sql)
            except Exception as e:
                db.connect.rollback()
                print(e)

def database_dump(datasname):
    '''保存数据库，包括建表语句，及表数据'''
    datas = {}
    tables = get_mysql_data('show tables')
    tables = [item.get(f'Tables_in_{datasname}') for item in tables]
    # print(tables)
    create_sql = []
    table_datas =  {}
    for table in tables:
        sqls = get_mysql_data(f'show create table {table}')
        if sqls[0].get('Create Table'):
            print(sqls[0].get('Create Table').replace('CREATE TABLE','CREATE TABLE IF NOT EXISTS'))
            print()
            create_sql.append(sqls[0].get('Create Table').replace('CREATE TABLE','CREATE TABLE IF NOT EXISTS'))
            table_data = get_mysql_data(f"select * from {table}")
            table_datas[table] = table_data
    datas['create_sqls'] = create_sql
    datas['table_datas'] = table_datas
    file_save(datasname,datas)

def database_load(datasname):
    '''恢复数据库，不含建库语名'''
    datas = file_load(datasname)
    sqls = datas.get('create_sqls')
    post_mysql(*sqls)
    for tablename,value in datas.get('table_datas').items():
        print(tablename)
        replace_mysql(tablename,value)

if __name__ == "__main__":
    sql ="""
        select 
        XM,OutHosDiseaseName,
        case
        when MobilePhone is not null then MobilePhone
        else c_tel end as MobilePhone,
        PrimaryDoctor,InHospitalCode,LastModify_TIME ,c_ssmc1_yb_desc 
        from patient_info a 
        left join patient_details b on c_zyh =InHospitalCode  
        where c_ssmc1_yb_desc is not null and month(lastModify_TIME)=12 and year(lastModify_TIME)=year(now())-1
        order by LastModify_TIME"""
    for  item in get_mysql_data(sql):
        for a in item.values():
            print(a,end=' ')
        print()
